﻿using CPPEI.PM.Service.Model.Repository;
using CPPEI.Utility;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

namespace CPPEI.PM.Service.Model
{
    public class PMService
    {
        /// <summary>
        /// 创建项目
        /// </summary>
        /// <param name="account"></param>
        /// <param name="caption"></param>
        /// <returns></returns>
        public ServiceModel.ServiceResult CreateProject(string account, string caption)
        {
            // 创建项目
            Project project = new Project();
            project.Id = Guid.NewGuid();
            project.Caption = caption;
            project.CreatedTime = DateTime.Now;
            project.Creator = account;
            string sql = "insert into [PM_Project] (Id,Caption,CreatedTime,Creator) values (@Id,@Caption,@CreatedTime,@Creator)";
            SqlParameter PId = new SqlParameter("@Id", project.Id);
            SqlParameter pCaption = new SqlParameter("@Caption", project.Caption);
            SqlParameter pCreatedTime = new SqlParameter("@CreatedTime", project.CreatedTime);
            SqlParameter pCreator = new SqlParameter("@Creator", project.Creator);
            SqlParameter[] sqlParameterArr = new SqlParameter[] { PId, pCaption, pCreatedTime, pCreator };
            DataAccess.DB.ExecuteNonQuery(sql, sqlParameterArr);

            // 创建权限
            CPPEI.Service.SystemManagement.Privilege privilege = new CPPEI.Service.SystemManagement.Privilege();
            privilege.Id = Guid.NewGuid();
            privilege.Caption = project.Caption;
            privilege.SortIndex = 0;
            privilege.Code = project.Id.ToString();
            privilege.TargetType = 0x00F2;
            privilege.TargetId = project.Id.ToString();
            privilege.CreatedTime = project.CreatedTime;
            privilege.Creator = project.Creator;
            new CPPEI.Service.SystemManagement.SystemManagementService().CreatePrivilege(privilege);

            // 创建一个导航
            CPPEI.Service.SystemManagement.Navigation navigation = new CPPEI.Service.SystemManagement.Navigation();
            navigation.Id = Guid.NewGuid();
            navigation.Caption = "项目导航";
            navigation.TargetType = BD_NavigationTargetType.Project;
            navigation.TargetId = project.Id.ToString();
            navigation.CreatedTime = DateTime.Now;
            navigation.Creator = account;
            new CPPEI.Service.SystemManagement.SystemManagementService().CreateNavigation(Guid.Empty, navigation);

            // 创建一个默认的工作表
            CreateDefaultWorkSheet(account, project.Id);

            return new ServiceModel.ServiceResult();
        }

        /// <summary>
        /// 创建一个默认的工作表
        /// </summary>
        /// <param name="account"></param>
        /// <param name="projectId"></param>
        /// <returns></returns>
        public ServiceModel.SingleResult<WorkSheet> CreateDefaultWorkSheet(string account, Guid projectId)
        {
            string workSheetName = "工作表1";
            ServiceModel.SingleResult<WorkSheet> result = CreateWorkSheet(account, projectId, workSheetName, Guid.Empty);

            // 创建工作项类别
            WorkItemCategory workItemCategory = new WorkItemCategory();
            workItemCategory.Id = Guid.NewGuid();
            workItemCategory.ProjectId = projectId;
            workItemCategory.Code = Guid.NewGuid().ToString("N");
            workItemCategory.Caption = "工作任务";
            workItemCategory.CreatedTime = DateTime.Now;
            workItemCategory.Creator = account;
            CreateWorkItemCategory(workItemCategory);

            // 创建一个工作项
            WorkItem workItem = new WorkItem();
            workItem.Id = Guid.NewGuid();
            workItem.Code = Guid.NewGuid().ToString("N");
            workItem.Caption = "工作项1";
            workItem.ProjectId = projectId;
            workItem.ParentId = Guid.Empty;
            workItem.WorkItemCategoryId = workItemCategory.Id;
            workItem.CreatedTime = DateTime.Now;
            workItem.Creator = account;
            CreateWorkItem(workItem);

            // 关联工作项类别
            RelateWorkItemCategory(result.Data.Id, Guid.Empty, workItemCategory.Id);

            // 关联工作项
            RelateWorkItem(result.Data.Id, workItemCategory.Id, Guid.Empty, workItem.Id);

            //创建阶段
            WorkStage workStage = new WorkStage();
            workStage.Id = Guid.NewGuid();
            workStage.Caption = "阶段";
            workStage.ProjectId = projectId;
            workStage.Code = Guid.NewGuid().ToString("N");
            workStage.ParentId = Guid.Empty;
            workStage.Level = 0;
            workStage.CreatedTime = DateTime.Now;
            workStage.Creator = account;
            CreateWorkStage(workStage);

            // 关联工作阶段
            RelateWorkStage(result.Data.Id, Guid.Empty, workStage.Id);

            string[] workStageItemArr = new string[] {"负责人","计划完成时间","实际完成时间","状态","备注","验收人" };
            for (int index = 0; index < workStageItemArr.Length; index++)
            {
                // 创建工作阶段
                WorkStageItem workStageItem = new WorkStageItem();
                workStageItem.Id = Guid.NewGuid();
                workStageItem.Caption = workStageItemArr[index];
                workStageItem.ProjectId = projectId;
                workStageItem.Code = Guid.NewGuid().ToString("N");
                workStageItem.ParentId = Guid.Empty;
                workStageItem.WorkStageId = workStage.Id;
                workStageItem.Level = 0;
                workStageItem.CreatedTime = DateTime.Now;
                workStageItem.Creator = account;
                CreateWorkStageItem(workStageItem);

                // 关联工作阶段的工作项
                RelateWorkStageItem(result.Data.Id, workStage.Id, Guid.Empty, workStageItem.Id);
            }


            return new ServiceModel.SingleResult<WorkSheet>(true, result.Data);
        }

        /// <summary>
        /// 创建工作表
        /// </summary>
        /// <param name="caption"></param>
        /// <returns></returns>
        public ServiceModel.SingleResult<WorkSheet> CreateWorkSheet(string account, Guid projectId, string caption, Guid workItemCategoryId)
        {
            // 创建工作表
            WorkSheet workSheet = new WorkSheet();
            workSheet.Id = Guid.NewGuid();
            workSheet.Caption = caption;
            workSheet.CreatedTime = DateTime.Now;
            workSheet.Creator = account;
            string sql = "insert into [PM_WorkSheet] (Id,Caption,ProjectId,CreatedTime,Creator) values (@Id,@Caption,@ProjectId,@CreatedTime,@Creator)";
            SqlParameter PId = new SqlParameter("@Id", workSheet.Id);
            SqlParameter pCaption = new SqlParameter("@Caption", workSheet.Caption);
            SqlParameter pProjectId = new SqlParameter("@ProjectId", projectId);
            SqlParameter pCreatedTime = new SqlParameter("@CreatedTime", workSheet.CreatedTime);
            SqlParameter pCreator = new SqlParameter("@Creator", workSheet.Creator);
            SqlParameter[] sqlParameterArr = new SqlParameter[] { PId, pCaption, pProjectId, pCreatedTime, pCreator };
            DataAccess.DB.ExecuteNonQuery(sql, sqlParameterArr);

            // 分配工作表的权限
            CPPEI.Service.SystemManagement.Privilege privilege = new CPPEI.Service.SystemManagement.Privilege();
            privilege.Id = Guid.NewGuid();
            privilege.Caption = workSheet.Caption;
            privilege.SortIndex = 0;
            privilege.Code = workSheet.Id.ToString();
            privilege.TargetType = 0x00F1;
            privilege.TargetId = workSheet.Id.ToString();
            privilege.CreatedTime = workSheet.CreatedTime;
            privilege.Creator = workSheet.Creator;
            new CPPEI.Service.SystemManagement.SystemManagementService().CreatePrivilege(privilege);

            // 创建导航项目
            CPPEI.Service.SystemManagement.Navigation projectNavigation = new CPPEI.Service.SystemManagement.SystemManagementService().GetNavigation(account, BD_NavigationTargetType.Project, projectId.ToString());
            CPPEI.Service.SystemManagement.Navigation navigation = new CPPEI.Service.SystemManagement.Navigation();
            navigation.Id = Guid.NewGuid();
            navigation.Caption = caption;
            navigation.TargetType = 0x00F1;
            navigation.TargetId = workSheet.Id.ToString();
            navigation.CreatedTime = DateTime.Now;
            navigation.Creator = account;

            new CPPEI.Service.SystemManagement.SystemManagementService().CreateNavigation(projectNavigation.Id, navigation);

            // 分配导航项目的权限

            ServiceModel.SingleResult<WorkSheet> result = new ServiceModel.SingleResult<WorkSheet>();

            if (workItemCategoryId != Guid.Empty)
            {
                // 关联工作项类别
                RelateWorkItemCategory(workSheet.Id, Guid.Empty, workItemCategoryId);

                List<WorkItem> workItemList = GetWorkItemList(workItemCategoryId);
                for (int index = 0; index < workItemList.Count; index++)
                {
                    // 关联工作项
                    RelateWorkItem(workSheet.Id, workItemCategoryId, Guid.Empty, workItemList[index].Id);
                }

                //创建阶段
                WorkStage workStage = new WorkStage();
                workStage.Id = Guid.NewGuid();
                workStage.Caption = "阶段";
                workStage.ProjectId = projectId;
                workStage.Code = Guid.NewGuid().ToString("N");
                workStage.ParentId = Guid.Empty;
                workStage.Level = 0;
                workStage.CreatedTime = DateTime.Now;
                workStage.Creator = account;
                CreateWorkStage(workStage);

                // 关联工作阶段
                RelateWorkStage(workSheet.Id, Guid.Empty, workStage.Id);

                string[] workStageItemArr = new string[] { "负责人", "计划完成时间", "实际完成时间", "状态", "备注", "验收人" };
                for (int index = 0; index < workStageItemArr.Length; index++)
                {
                    // 创建工作阶段
                    WorkStageItem workStageItem = new WorkStageItem();
                    workStageItem.Id = Guid.NewGuid();
                    workStageItem.Caption = workStageItemArr[index];
                    workStageItem.ProjectId = projectId;
                    workStageItem.Code = Guid.NewGuid().ToString("N");
                    workStageItem.ParentId = Guid.Empty;
                    workStageItem.WorkStageId = workStage.Id;
                    workStageItem.Level = 0;
                    workStageItem.CreatedTime = DateTime.Now;
                    workStageItem.Creator = account;
                    CreateWorkStageItem(workStageItem);

                    // 关联工作阶段的工作项
                    RelateWorkStageItem(workSheet.Id, workStage.Id, Guid.Empty, workStageItem.Id);
                }
            }

            result.Data = workSheet;
            return result;
        }

        /// <summary>
        /// 创建工作项类别
        /// </summary>
        /// <param name="workItemCategory"></param>
        /// <returns></returns>
        public WorkItemCategory CreateWorkItemCategory(WorkItemCategory workItemCategory)
        {
            using (PMDBContext dbContext = new PMDBContext())
            {
                CreateWorkItemCategory(dbContext, workItemCategory);

                dbContext.SaveChanges();
            }
            return workItemCategory;
        }

        public WorkItemCategory CreateWorkItemCategory(PMDBContext dbContext, WorkItemCategory workItemCategory)
        {
            WorkItemCategoryRepository wicRep = new WorkItemCategoryRepository(dbContext);
            wicRep.Create(workItemCategory);

            return workItemCategory;
        }

        /// <summary>
        /// 创建工作项，此项目会自动关联到其他的工作表上
        /// </summary>
        /// <param name="workItem"></param>
        /// <returns></returns>
        public bool CreateWorkItemAndAttachWorkSheet(WorkItem workItem)
        {
            using (PMDBContext dbContext = new PMDBContext())
            {
                WorkItemRepository wiRep = new WorkItemRepository(dbContext);
                WorkItemCategoryRefRepository wicrRep = new WorkItemCategoryRefRepository(dbContext);

                wiRep.CreateWorkItem(workItem);

                // 获取此项目的类别所关联的工作表
                List<Guid> workSheetIdList = wicrRep.GetRelatedWorkSheetIdList(workItem.WorkItemCategoryId);
                for (int index = 0; index < workSheetIdList.Count; index++)
                {
                    RelateWorkItem(workSheetIdList[index], workItem.WorkItemCategoryId, workItem.ParentId, workItem.Id);
                }

                dbContext.SaveChanges();
            }
            return true;
        }

        /// <summary>
        /// 创建工作项
        /// </summary>
        /// <param name="workItem"></param>
        /// <returns></returns>
        public ServiceModel.SingleResult<WorkItem> CreateWorkItem(WorkItem workItem)
        {
            using (PMDBContext dbContext = new PMDBContext())
            {
                CreateWorkItem(dbContext, workItem);
                dbContext.SaveChanges();
            }

            return new ServiceModel.SingleResult<WorkItem>(true, workItem);
        }

        /// <summary>
        /// 创建工作项
        /// </summary>
        /// <param name="dbContext"></param>
        /// <param name="workItem"></param>
        /// <returns></returns>
        private WorkItem CreateWorkItem(PMDBContext dbContext, WorkItem workItem)
        {
            WorkItemRepository wiRep = new WorkItemRepository(dbContext);
            wiRep.CreateWorkItem(workItem);
            return workItem;
        }

        public WorkStage CreateWorkStage(WorkStage workStage)
        {
            using (PMDBContext dbContext = new PMDBContext())
            {
                WorkStageRepository wsRep = new WorkStageRepository(dbContext);

                wsRep.Create(workStage);

                dbContext.SaveChanges();
            }
            return workStage;
        }

        public WorkStageItem CreateWorkStageItem(WorkStageItem workStageItem)
        {
            using (PMDBContext dbContext = new PMDBContext())
            {
                WorkStageItemRepository wsiRep = new WorkStageItemRepository(dbContext);

                wsiRep.Create(workStageItem);

                dbContext.SaveChanges();
            }
            return workStageItem;
        }

        public ServiceModel.ListResult<Project> GetProjectList(string account)
        {
            ServiceModel.ListResult<Project> listResult = new ServiceModel.ListResult<Project>();

            string sql = "select [Id],[Caption],[CreatedTime],[Creator] from [PM_Project]";
            DataTable dataTable = DataAccess.DB.QueryDataTable(sql);
            for (int index = 0; index < dataTable.Rows.Count; index++)
            {
                Project project = new Project();
                project.Id = new Guid(Convert.ToString(dataTable.Rows[index]["Id"]));
                project.Caption = Convert.ToString(dataTable.Rows[index]["Caption"]);
                project.CreatedTime = Convert.ToDateTime(dataTable.Rows[index]["CreatedTime"]);
                project.Creator = Convert.ToString(dataTable.Rows[index]["Creator"]);
                listResult.Datas.Add(project);
            }

            return listResult;
        }

        public ServiceModel.SingleResult<WorkSheet> GetWorkSheet(Guid projectId, Guid workSheetId)
        {
            ServiceModel.SingleResult<WorkSheet> result = new ServiceModel.SingleResult<WorkSheet>();

            string sql = "select [Id],[ProjectId],[Caption],[CreatedTime],[Creator] from [PM_WorkSheet] where ProjectId=@ProjectId and Id=@WorkSheetId";
            SqlParameter pProjectId = new SqlParameter("@ProjectId", projectId);
            SqlParameter pWorkSheetId = new SqlParameter("@WorkSheetId ", workSheetId);

            DataTable dataTable = DataAccess.DB.QueryDataTable(sql, new SqlParameter[] { pProjectId, pWorkSheetId });
            for (int index = 0; index < dataTable.Rows.Count; index++)
            {
                WorkSheet workSheet = new WorkSheet();
                workSheet.Id = new Guid(Convert.ToString(dataTable.Rows[index]["Id"]));
                workSheet.Caption = Convert.ToString(dataTable.Rows[index]["Caption"]);
                workSheet.CreatedTime = Convert.ToDateTime(dataTable.Rows[index]["CreatedTime"]);
                workSheet.Creator = Convert.ToString(dataTable.Rows[index]["Creator"]);
                result.Data = workSheet;
            }

            return result;
        }

        /// <summary>
        /// 关联工作项类别到工作表
        /// </summary>
        /// <param name="workSheetId"></param>
        /// <param name="parentWorkItemCategoryId"></param>
        /// <param name="workItemCategoryId"></param>
        public void RelateWorkItemCategory(Guid workSheetId, Guid parentWorkItemCategoryId,
            Guid workItemCategoryId)
        {
            using(PMDBContext dbContext = new PMDBContext())
            {
                WorkItemCategoryRefRepository wicrRep = new WorkItemCategoryRefRepository(dbContext);

                WorkItemCategoryRef workItemCategoryRef = new WorkItemCategoryRef();
                workItemCategoryRef.WorkSheetId = workSheetId;
                workItemCategoryRef.WorkItemCategoryId = workItemCategoryId;
                workItemCategoryRef.ParentWorkItemcategoryId = parentWorkItemCategoryId;
                if (parentWorkItemCategoryId != Guid.Empty)
                {
                    WorkItemCategoryRef parentWorkItemCategoryRef = wicrRep.Get(workSheetId, workItemCategoryId);
                    workItemCategoryRef.Level = parentWorkItemCategoryRef.Level + 1;
                }
                else
                {
                    workItemCategoryRef.Level = 0;
                }

                wicrRep.Create(workItemCategoryRef);

                dbContext.SaveChanges();
            }
        }

        /// <summary>
        /// 引用工作项到所有有关的工作表
        /// </summary>
        /// <param name="workItemCategoryId"></param>
        /// <param name="parentWorkItemId"></param>
        /// <param name="workItemId"></param>
        public void RelateWorkItemToRelatedWorkSheets(Guid workItemCategoryId,
            Guid parentWorkItemId,
            Guid workItemId)
        {
            using (PMDBContext dbContext = new PMDBContext())
            {
                WorkItemRefRepository wirRep = new WorkItemRefRepository(dbContext);

                WorkItemCategoryRefRepository wicrRep = new WorkItemCategoryRefRepository(dbContext);

                // 获取此项目的类别所关联的工作表
                List<Guid> workSheetIdList = wicrRep.GetRelatedWorkSheetIdList(workItemCategoryId);
                for (int index = 0; index < workSheetIdList.Count; index++)
                {
                    RelateWorkItem(dbContext, workSheetIdList[index], workItemCategoryId, parentWorkItemId, workItemId);
                }

                dbContext.SaveChanges();
            }
        }

        public void RelateWorkItem(Guid workSheetId,
            Guid workItemCategoryId,
            Guid parentWorkItemId,
            Guid workItemId)
        {
            using (PMDBContext dbContext = new PMDBContext())
            {
                WorkItemRefRepository wirRep = new WorkItemRefRepository(dbContext);

                WorkItemRef workItemRef = new WorkItemRef();
                workItemRef.WorkSheetId = workSheetId;
                workItemRef.WorkItemId = workItemId;
                workItemRef.WorkItemCategoryId = workItemCategoryId;
                workItemRef.ParentWorkItemId = parentWorkItemId;
                if (parentWorkItemId != Guid.Empty)
                {
                    WorkItemRef parentWorkItemRef = wirRep.Get(workSheetId, parentWorkItemId);
                    workItemRef.Level = parentWorkItemRef.Level + 1;
                }
                else
                {
                    workItemRef.Level = 0;
                }

                wirRep.Create(workItemRef);

                dbContext.SaveChanges();
            }
        }

        private void RelateWorkItem(PMDBContext dbContext,
            Guid workSheetId,
            Guid workItemCategoryId,
            Guid parentWorkItemId,
            Guid workItemId)
        {
            WorkItemRefRepository wirRep = new WorkItemRefRepository(dbContext);

            WorkItemRef workItemRef = new WorkItemRef();
            workItemRef.WorkSheetId = workSheetId;
            workItemRef.WorkItemId = workItemId;
            workItemRef.WorkItemCategoryId = workItemCategoryId;
            workItemRef.ParentWorkItemId = parentWorkItemId;
            if (parentWorkItemId != Guid.Empty)
            {
                WorkItemRef parentWorkItemRef = wirRep.Get(workSheetId, parentWorkItemId);
                workItemRef.Level = parentWorkItemRef.Level + 1;
            }
            else
            {
                workItemRef.Level = 0;
            }

            wirRep.Create(workItemRef);
        }

        public void RelateWorkStage(Guid workSheetId, Guid parentWorkStageId, Guid workStageId)
        {
            using (PMDBContext dbContext = new PMDBContext())
            {
                WorkStageRefRepository wsrRep = new WorkStageRefRepository(dbContext);

                WorkStageRef workStageRef = new WorkStageRef();
                workStageRef.WorkSheetId = workSheetId;
                workStageRef.WorkStageId = workStageId;
                workStageRef.ParentWorkStageId = parentWorkStageId;

                if (parentWorkStageId != Guid.Empty)
                {
                    WorkStageRef parentWorkStageRef = wsrRep.Get(workSheetId, parentWorkStageId);
                    workStageRef.Level = parentWorkStageRef.Level + 1;
                }
                else
                {
                    workStageRef.Level = 0;
                }

                wsrRep.Create(workStageRef);

                dbContext.SaveChanges();
            }
        }

        public void RelateWorkStageItem(Guid workSheetId, Guid workStageId, Guid parentWorkStageItemId, Guid workStageItemId)
        {
            using (PMDBContext dbContext = new PMDBContext())
            {
                WorkStageItemRefRepository wsirRep = new WorkStageItemRefRepository(dbContext);

                WorkStageItemRef workStageItemRef = new WorkStageItemRef();
                workStageItemRef.WorkSheetId = workSheetId;
                workStageItemRef.WorkStageId = workStageId;
                workStageItemRef.WorkStageItemId = workStageItemId;
                workStageItemRef.ParentWorkStageItemId = parentWorkStageItemId;

                if (parentWorkStageItemId != Guid.Empty)
                {
                    WorkStageItemRef parentWorkStageItemRef = wsirRep.Get(workSheetId, parentWorkStageItemId);
                    workStageItemRef.Level = parentWorkStageItemRef.Level + 1;
                }
                else
                {
                    workStageItemRef.Level = 0;
                }

                wsirRep.Create(workStageItemRef);

                dbContext.SaveChanges();
            }
        }

        /// <summary>
        /// 获取工作表中的工作项类别
        /// </summary>
        /// <param name="workSheetId"></param>
        /// <returns></returns>
        public Domain.WorkItemCategoryRef GetWorkItemCategory4WorkSheet(Guid workSheetId)
        {
            using (PMDBContext dbContext = new PMDBContext())
            {
                WorkItemCategoryRefRepository wicrRep = new WorkItemCategoryRefRepository(dbContext);
                WorkItemCategoryRepository wicRep = new WorkItemCategoryRepository(dbContext);

                WorkItemCategoryRef workItemCategoryRef = wicrRep.Get(workSheetId);
                WorkItemCategory workItemCategory = wicRep.Get(workItemCategoryRef.WorkItemCategoryId);

                Domain.WorkItemCategoryRef model = new Domain.WorkItemCategoryRef();
                model.Id = workItemCategory.Id;
                model.Code = workItemCategory.Code;
                model.Caption = workItemCategory.Caption;
                model.Level = workItemCategoryRef.Level;
                model.ParentId = workItemCategoryRef.ParentWorkItemcategoryId;
                return model;
            }
        }

        public List<Domain.WorkItemCategoryRef> GetWorkItemCategory4WorkSheet(Guid workSheetId, Guid parentWorkItemCategoryId)
        {
            using (PMDBContext dbContext = new PMDBContext())
            {
                List<Domain.WorkItemCategoryRef> modelList = new List<Domain.WorkItemCategoryRef>();

                WorkItemCategoryRefRepository wicrRep = new WorkItemCategoryRefRepository(dbContext);
                WorkItemCategoryRepository wicRep = new WorkItemCategoryRepository(dbContext);

                List<WorkItemCategoryRef> workItemCategoryRefList = wicrRep.GetByParent(workSheetId, parentWorkItemCategoryId);

                for (int index = 0; index < workItemCategoryRefList.Count; index++)
                {
                    WorkItemCategoryRef workItemCategoryRef = workItemCategoryRefList[index];
                    WorkItemCategory workItemCategory = wicRep.Get(workItemCategoryRef.WorkItemCategoryId);

                    Domain.WorkItemCategoryRef model = new Domain.WorkItemCategoryRef();
                    model.Id = workItemCategory.Id;
                    model.Code = workItemCategory.Code;
                    model.Caption = workItemCategory.Caption;
                    model.Level = workItemCategoryRef.Level;
                    model.ParentId = workItemCategoryRef.ParentWorkItemcategoryId;
                    modelList.Add(model);
                }

                return modelList;
            }
        }

        public List<Domain.WorkItemRef> GetWorkItem4WorkSheet(Guid workSheetId, Guid workItemCategoryId, Guid parentWorkItemId)
        {
            using (PMDBContext dbContext = new PMDBContext())
            {
                List<Domain.WorkItemRef> modelList = new List<Domain.WorkItemRef>();

                WorkItemRefRepository wirRep = new WorkItemRefRepository(dbContext);
                WorkItemRepository wiRep = new WorkItemRepository(dbContext);

                List<WorkItemRef> workItemRefList = wirRep.GetByParent(workSheetId, workItemCategoryId, parentWorkItemId);

                for (int index = 0; index < workItemRefList.Count; index++)
                {
                    WorkItemRef workItemRef = workItemRefList[index];
                    WorkItem workItem = wiRep.Get(workItemRef.WorkItemId);

                    Domain.WorkItemRef model = new Domain.WorkItemRef();
                    model.Id = workItem.Id;
                    model.Code = workItem.Code;
                    model.Caption = workItem.Caption;
                    model.Level = workItem.Level;
                    model.ParentId = workItemRef.ParentWorkItemId;
                    modelList.Add(model);
                }

                return modelList;
            }
        }

        public Domain.WorkStageRef GetRootWorkStage4WorkSheet(Guid workSheetId)
        {
            using (PMDBContext dbContext = new PMDBContext())
            {
                WorkStageRefRepository wsrRep = new WorkStageRefRepository(dbContext);
                WorkStageRepository wsRep = new WorkStageRepository(dbContext);

                WorkStageRef workStageRef = wsrRep.GetRoot(workSheetId);
                WorkStage workStage = wsRep.Get(workStageRef.WorkStageId);

                Domain.WorkStageRef model = new Domain.WorkStageRef();
                model.Id = workStage.Id;
                model.Code = workStage.Code;
                model.Caption = workStage.Caption;
                model.Level = workStage.Level;
                model.ParentId = workStageRef.ParentWorkStageId;
                return model;
            }
        }

        public List<Domain.WorkStageRef> GetWorkStageList4WorkSheet(Guid workSheetId, Guid parentWorkStageId)
        {
            using (PMDBContext dbContext = new PMDBContext())
            {
                List<Domain.WorkStageRef> modelList = new List<Domain.WorkStageRef>();

                WorkStageRefRepository wsrRep = new WorkStageRefRepository(dbContext);
                WorkStageRepository wsRep = new WorkStageRepository(dbContext);

                List<WorkStageRef> workStageRefList = wsrRep.GetByParent(workSheetId, parentWorkStageId);
                for (int index = 0; index < workStageRefList.Count; index++)
                {
                    WorkStageRef workStageRef = workStageRefList[index];
                    WorkStage workStage = wsRep.Get(workStageRef.WorkStageId);

                    Domain.WorkStageRef model = new Domain.WorkStageRef();
                    model.Id = workStage.Id;
                    model.Code = workStage.Code;
                    model.Caption = workStage.Caption;
                    model.Level = workStage.Level;
                    model.ParentId = workStageRef.ParentWorkStageId;
                    modelList.Add(model);
                }

                return modelList;
            }
        }

        public List<Domain.WorkStageItemRef> GetWorkStageItem4WorkSheet(Guid workSheetId, Guid workStageId, Guid parentWorkStageItemId)
        {
            using (PMDBContext dbContext = new PMDBContext())
            {
                List<Domain.WorkStageItemRef> modelList = new List<Domain.WorkStageItemRef>();

                WorkStageItemRefRepository wsirRep = new WorkStageItemRefRepository(dbContext);
                WorkStageItemRepository wsiRep = new WorkStageItemRepository(dbContext);

                List<WorkStageItemRef> workStageItemRefList = wsirRep.GetByParent(workSheetId, workStageId, parentWorkStageItemId);
                for (int index = 0; index < workStageItemRefList.Count; index++)
                {
                    WorkStageItemRef workStageItemRef = workStageItemRefList[index];
                    WorkStageItem workStageItem = wsiRep.Get(workStageItemRef.WorkStageItemId);

                    Domain.WorkStageItemRef model = new Domain.WorkStageItemRef();
                    model.Id = workStageItem.Id;
                    model.Code = workStageItem.Code;
                    model.Caption = workStageItem.Caption;
                    model.Level = workStageItem.Level;
                    model.ParentId = workStageItemRef.ParentWorkStageItemId;
                    modelList.Add(model);
                }

                return modelList;
            }
        }

        public WorkItemCategory GetWorkItemCategory(Guid projectId, Guid workItemCategoryId)
        {
            WorkItemCategory workItemCategory = null;

            string sql = @"select Id,ProjectId,Caption,[Level],ParentId,CreatedTime,Creator from PM_WorkItemCategory 
    where ProjectId=@ProjectId and Id=@Id";
            SqlParameter pProjectId = new SqlParameter("@ProjectId", projectId);
            SqlParameter pId = new SqlParameter("@Id ", workItemCategoryId);

            DataTable dataTable = DataAccess.DB.QueryDataTable(sql, new SqlParameter[] { pProjectId, pId });
            for (int index = 0; index < dataTable.Rows.Count; index++)
            {
                workItemCategory = new WorkItemCategory();
                workItemCategory.Id = new Guid(Convert.ToString(dataTable.Rows[index]["Id"]));
                workItemCategory.Caption = Convert.ToString(dataTable.Rows[index]["Caption"]);
                workItemCategory.CreatedTime = Convert.ToDateTime(dataTable.Rows[index]["CreatedTime"]);
                workItemCategory.Creator = Convert.ToString(dataTable.Rows[index]["Creator"]);
                break;
            }

            return workItemCategory;
        }

        public List<WorkItem> GetWorkItemList(Guid workItemCategoryId)
        {
            using (PMDBContext dbContext = new PMDBContext())
            {
                WorkItemRepository wiRep = new WorkItemRepository(dbContext);

                return wiRep.GetList(workItemCategoryId);
            }
        }

        public WorkItem GetWorkItem(Guid projectId, Guid workItemCategoryId, Guid workItemId)
        {
            WorkItem result = null;

            string sql = @"select Id,ProjectId,Caption,WorkItemCategoryId,[Level],ParentId,CreatedTime,Creator from PM_WorkItem 
    where ProjectId=@ProjectId and WorkItemCategoryId=@WorkItemCategoryId and Id=@Id";
            SqlParameter pProjectId = new SqlParameter("@ProjectId", projectId);
            SqlParameter pWorkItemCategoryId = new SqlParameter("@WorkItemCategoryId ", workItemCategoryId);
            SqlParameter pId = new SqlParameter("@Id ", workItemId);

            DataTable dataTable = DataAccess.DB.QueryDataTable(sql, new SqlParameter[] { pProjectId, pWorkItemCategoryId, pId });
            for (int index = 0; index < dataTable.Rows.Count; index++)
            {
                result = new WorkItem();
                result.Id = new Guid(Convert.ToString(dataTable.Rows[index]["Id"]));
                result.Caption = Convert.ToString(dataTable.Rows[index]["Caption"]);
                result.CreatedTime = Convert.ToDateTime(dataTable.Rows[index]["CreatedTime"]);
                result.Creator = Convert.ToString(dataTable.Rows[index]["Creator"]);
            }

            return result;
        }

        public void SaveWorkSheetCell(Guid projectId, Guid workSheetId, WorkSheetCell cell)
        {
            string querySql = "select Id from [PM_WorkSheetCell] where ProjectId=@ProjectId and WorkSheetId=@WorkSheetId and Path=@Path";
            SqlParameter pProjectId = new SqlParameter("@ProjectId", projectId);
            SqlParameter pWorkSheetId = new SqlParameter("@WorkSheetId", workSheetId);
            SqlParameter pPath = new SqlParameter("@Path", cell.Path);
            DataTable dt = DataAccess.DB.QueryDataTable(querySql, new SqlParameter[] { pProjectId, pWorkSheetId, pPath });

            if (dt != null && dt.Rows.Count > 0)
            {
                // update
                string updateSql = "update [PM_WorkSheetCell] set [Value]=@Value,[Text]=@Text where ProjectId=@ProjectId and WorkSheetId=@WorkSheetId and Path=@Path";
                SqlParameter pUpdateProjectId = new SqlParameter("@ProjectId", projectId);
                SqlParameter pUpdateWorkSheetId = new SqlParameter("@WorkSheetId", workSheetId);
                SqlParameter pUpdatePath = new SqlParameter("@Path", cell.Path);
                SqlParameter pUpdateValue = new SqlParameter("@Value", cell.Value);
                SqlParameter pUpdateText = new SqlParameter("@Text", cell.Text);
                DataAccess.DB.ExecuteNonQuery(updateSql, new SqlParameter[] { pUpdateProjectId, pUpdateWorkSheetId, pUpdatePath, pUpdateValue, pUpdateText });
            }
            else
            {
                // insert
                string insertSql = "insert into [PM_WorkSheetCell] (Id,ProjectId,WorkSheetId,[Path],[Value],[Text]) values (@Id,@ProjectId,@WorkSheetId,@Path,@Value,@Text)";
                SqlParameter pId = new SqlParameter("@Id", Guid.NewGuid());
                SqlParameter pIntertProjectId = new SqlParameter("@ProjectId", projectId);
                SqlParameter pIntertWorkSheetId = new SqlParameter("@WorkSheetId", workSheetId);
                SqlParameter pIntertPath = new SqlParameter("@Path", cell.Path);
                SqlParameter pIntertValue = new SqlParameter("@Value", cell.Value);
                SqlParameter pIntertText = new SqlParameter("@Text", cell.Text);
                DataAccess.DB.ExecuteNonQuery(insertSql, new SqlParameter[] { pId, pIntertProjectId, pIntertWorkSheetId, pIntertPath, pIntertValue, pIntertText });
            
            }
        }

        public List<WorkSheetCell> GetWorkSheetCells(Guid projectId, Guid workSheetId)
        {
            List<WorkSheetCell> list = new List<WorkSheetCell>();

            string querySql = "select Id,[Path],[Value],[Text] from [PM_WorkSheetCell] where ProjectId=@ProjectId and WorkSheetId=@WorkSheetId";
            SqlParameter pProjectId = new SqlParameter("@ProjectId", projectId);
            SqlParameter pWorkSheetId = new SqlParameter("@WorkSheetId", workSheetId);
            DataTable dt = DataAccess.DB.QueryDataTable(querySql, new SqlParameter[] { pProjectId, pWorkSheetId });
            for (int index = 0; index < dt.Rows.Count; index++)
            {
                WorkSheetCell cell = new WorkSheetCell();
                cell.Path = Convert.ToString(dt.Rows[index]["Path"]);
                cell.Value = Convert.ToString(dt.Rows[index]["Value"]);
                cell.Text = Convert.ToString(dt.Rows[index]["Text"]);
                list.Add(cell);
            }

            return list;
        }

        public WorkItemCategory GetWorkItemCategoryByCode(Guid projectId, string workItemCategoryCode)
        {
            using (PMDBContext dbContext = new PMDBContext())
            {
                WorkItemCategoryRepository wicRep = new WorkItemCategoryRepository(dbContext);
                return wicRep.GetByCode(projectId, workItemCategoryCode);
            }
        }

        public WorkItem GetWorkItemByCode(Guid projectId, Guid workItemCategoryId, string workItemCode)
        {
            using (PMDBContext dbContext = new PMDBContext())
            {
                WorkItemRepository wiRep = new WorkItemRepository(dbContext);
                return wiRep.GetByCode(projectId, workItemCategoryId, workItemCode);
            }
        }
    }
}
